/*
 * Main.scala
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package org.squela
package sql

import java.util.Date
import org.squela._
import sql._

object Main {
  import QueryBuilder._

  def main1(args: Array[String]) :Unit = {
    println("Starting!")
    val builder = new SqlSyntaxBuilder(new StandardSqlSyntaxProvider)
    val engine = new QueryEngine {
      val syntaxBuilder: SqlSyntaxBuilder = builder
      def getConnection(): java.sql.Connection = null
    }
        
    val joinedTables = LeftJoin(SerieTable, ChapterTable, (SerieTable.pk.serieName, ChapterTable.pk.serieName),
                                (SerieTable.chaptersCount, ChapterTable.pk.chapterName))
    val subQuery = select(SerieTable)(SerieTable.fields:_*) where SerieTable.pk.serieName->"Alberto Gandulia"
    println(builder build subQuery)
    var query = select(joinedTables)(SerieTable.pk.serieName.max, ChapterTable.fecha) where
    (SerieTable.pk.serieName->"Alberto" && SerieTable.fecha>new java.util.Date() || ((SerieTable.pk.serieName in subQuery) &&
                                                                                     SerieTable.fecha->new java.util.Date()))
    println(builder.build(query))
    //        engine.executeQuery(query, null) {qr: engine.QueryResult => qr.applyTo(new Serie())}
        
        
    query = insert("Series")(FieldAssociation.associate(new Serie("Alebrto", 4, new java.util.Date, null),
                                                        SerieTable.pk.serieName, SerieTable.fecha, SerieTable.pk.serieName):_*)
    println(builder.build(query))
    query = delete("Series") where (SerieTable.pk.serieName>="Alberto Gandulia")
    println(builder.build(query))
    query = update("Series")(SerieTable.pk.serieName->"Romualdo", SerieTable.fecha->new java.util.Date) where (SerieTable.pk.serieName>="Alberto Gandulia")
    println(builder.build(query))
    query = update("SuperSeries")(SerieTable.pk.serieName->null, SerieTable.fecha->new java.util.Date) where (SerieTable.pk.serieName->null && SerieTable.pk.serieSecondaryName<>null)
    println(builder.build(query))
    query = create(SerieTable)(SerieTable.fields:_*)
    println(builder.build(query))
    query = create(ChapterTable)(ChapterTable.fields:_*)
    println(builder.build(query))
    query = alterTable(ChapterTable)(AddColumn(ChapterTable.fecha))
    println(builder.build(query))
    query = alterTable(ChapterTable)(DropColumn(ChapterTable.fecha))
    println(builder.build(query))
    query = alterTable(ChapterTable)(ChangeColumnName("roberto", ChapterTable.fecha))
    println(builder.build(query))
    query = alterTable(ChapterTable)(ChangeColumnType(ChapterTable.fecha))
    println(builder.build(query))
    query = alterTable(ChapterTable)(AddPrimaryKey(SerieTable.pk.serieName))
    println(builder.build(query))
    query = alterTable(ChapterTable)(AddForeignKey(ChapterTable.pk.serieName.reference.get))
    println(builder.build(query))
    val serie = new Serie("s1", 4, new java.util.Date, null)
//    implicit val bindings = new Bindings + (SerieTable.pk.serieSecondaryName, Bindings.ClassField({obj: Serie => "romualdito"}, {(a: Serie, b: String) => ()}))
    println(builder.build(SerieTable.insert(serie)))
    println(builder.build(SerieTable.update(serie, SerieTable.chaptersCount)))
    println(builder.build(SerieTable.delete(serie)))
    println(builder.build(SerieTable.select((serie.name, 9))))

    println(builder.build(EventTable.select(null, EventTable.fields:_*)))
  }


  /**
   * @param args the command line arguments
   */
  def main(args: Array[String]) :Unit = {
    main1(args)
  }

}

private[sql] class Serie(val name: String, val chaptersCount: Int, val fecha: java.util.Date, val other: Any) extends AnyRef with NotNull {
  var chula = true
  def this() = this(null, 0, null, null)
}

private[sql] object SerieTable extends Table("Serie", "S") with BoundTableOperations[Serie] {
  implicit val self = this
  trait PK extends Table.PrimaryKeyBuilder {
    val serieName = PKField[String]("Name", 50)
    val serieSecondaryName = PKField[String]("SecondaryName", 50)
  }
  val pk = buildPrimaryKey(new PK{})
  val chaptersCount = Field[Int]("ChaptersCount")
  val fecha = Field[Date]("Fecha")

  val bindings = new Bindings {
    +=(pk.serieName, Bindings.ClassField[Serie, String](serie => "SerieName = " + serie.name,
                                                        (serie, name) => ""))
    +=(pk.serieSecondaryName, Bindings.ClassField[Serie, String](serie => "SerieSecondaryName = " + serie.name,
                                                                 (serie, name) => ""))
  }
}

private[sql] object ProductTable extends Table("Product", "P") {
  implicit val self = this
  trait PK extends Table.PrimaryKeyBuilder {
    override val onUpdate = Table.ConstraintAction.Restrict
    override val onDelete = Table.ConstraintAction.SetNull
    val pid = PKField[Int]("Id")
    val date = PKField[Date]("Date")
  }
  val pk = buildPrimaryKey(new PK{})
}

private[sql] object EventTable extends Table("Event", "E") with BoundTableOperations[AnyRef] {
  lazy val bindings: Bindings = null
  implicit val self = this
  trait PK extends ProductTable.PK with Table.PrimaryKeyBuilder{
    override val onUpdate = Table.ConstraintAction.SetNull
    override val onDelete = Table.ConstraintAction.Restrict
    val id = PKField[Int]("Id")
  }
  val pk = buildPrimaryKey(new PK{})
}

private[sql] object ChapterTable extends Table("Chapter", "C") {
  implicit val self = this
  trait PK extends Table.PrimaryKeyBuilder with SerieTable.PK with ProductTable.PK{
    val chapterName = PKField[String]("Name", 50)
  }
  val pk = buildPrimaryKey(new PK{})
//    val serieName = PKField(Reference(SerieTable.serieName, Table.ConstraintAction.Restrict, Table.ConstraintAction.SetNull))
//    val productId = PKField(Reference(ProductTable.primaryKey.pid, Table.ConstraintAction.Cascade, Table.ConstraintAction.Restrict))
  val fecha = Field[Date]("Fecha")
}
